<%@ Page language="vb" CodeFile="statisticsProductOverview.aspx.vb" Inherits="statisticsProductOverview_aspx_vb" %>

<%
    rowNumber = 1
    flushCounter = 0
    subDo = Request.QueryString("subDo")
    dateFrom = Request.QueryString("dateFrom")
    dateTo = Request.QueryString("dateTo")
    If Len(dateFrom) < 10 Then 
        dateFrom = Request.Form("dateFrom")
    End IF
    If Len(dateTo) < 10 Then 
        dateTo = Request.Form("dateTo")
    End IF
    If Len(dateFrom) = 10 And Len(dateTo) = 10 Then 
        If dateFrom > dateTo Then 
            dateFrom = ""
            dateTo = ""
        End IF
    End IF
    If Len(dateFrom) = 10 Then 
        sqlClaimDateFrom = " And Cast(Floor(Cast(c.date AS Float)) As DateTime) >= '" & dateFrom & "'"
        sqlVehicleDateFrom = " And Cast(Floor(Cast(v.date AS Float)) As DateTime) >= '" & dateFrom & "'"
        sqlInvoiceDateFrom = " And Cast(Floor(Cast(i.invoiceDate AS Float)) As DateTime) >= '" & dateFrom & "'"
    End IF
    If Len(dateTo) = 10 Then 
        sqlClaimDateTo = " And Cast(Floor(Cast(c.date AS Float)) As DateTime) <= '" & dateTo & "'"
        sqlVehicleDateTo = " And Cast(Floor(Cast(v.date AS Float)) As DateTime) <= '" & dateTo & "'"
        sqlInvoiceDateTo = " And Cast(Floor(Cast(i.invoiceDate AS Float)) As DateTime) <= '" & dateTo & "'"
    End IF
    If (Request.QueryString("do")) = Nothing Then 
        If subDo = "print" Then 
            tableWidth = "100%"
            OverPrint()
        Else
            tableWidth = "100%"
            Over()
        End IF
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='" & tableWidth & "'>")
        If subDo = "" Then 
            Response.Write("<tr>")
            Response.Write("<td valign='top'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
            Response.Write("<tr><td class='headline' colspan='3'>STATISTICS - PRODUCT OVERVIEW<hr class='hrHeadline'></td></tr>")
            Response.Write("<form action='statisticsProductOverview.aspx' method='post' name='store_productsSearch'>")
            Response.Write("<tr><td class='formBold'>Statistics period (YYYY-MM-DD):</td><td align='right'><input type='text' name='dateFrom' id='dateFrom' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'> - <input type='text' name='dateTo' id='dateTo' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
            Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Generate Results'></td></tr>")
            Response.Write("</form>")
            Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("<td width='100'></td>")
            Response.Write("<td valign='top'>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End IF
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='" & tableWidth & "'>")
        Response.Write("<tr>")
        Response.Write("<td class='headline' colspan='13'>")
        Response.Write("STATISTICS RESULT ")
        If Len(dateFrom) = 10 And Len(dateTo) = 10 Then 
            Response.Write("- BETWEEN " & dateFrom & " - " & dateTo & "")
        ElseIf Len(dateFrom) = 10 Then 
            Response.Write("- ON OR AFTER " & dateFrom & "")
        ElseIf Len(dateTo) = 10 Then 
            Response.Write("- ON OR BEFORE " & dateTo & "")
        Else
            Response.Write("- TOTAL")
        End IF
        If Len(subDo) = 0 Then 
            Response.Write("<a href='statisticsProductOverview.aspx?subDo=print&dateFrom=" & dateFrom & "&dateTo=" & dateTo & "' target='_blank'><img src='gui/printer.gif' align='right' alt='Printable version' border='0'></a>")
        End IF
        Response.Write("<hr class='hrHeadline'>")
        Response.Write("</td>")
        Response.Write("</tr>")
        dbOpen()
        RecSet = Conn.Execute("Select p.productID, p.product, p.date, c.country, c.countryCurrency From (tblProduct p INNER JOIN tblCountry c ON c.countryID = p.countryID) Order By p.product")
        If RecSet.EOF Then 
            Response.Write("<tr><td><font color='red'>No claims found</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Country</td>")
            Response.Write("<td class='formBold'>Product</td>")
            Response.Write("<td class='formBold' align='right'>Active LC</td>")
            Response.Write("<td class='formBold' align='right'>Inactive LC</td>")
            Response.Write("<td class='formBold' align='right'>Total LC</td>")
            Response.Write("<td class='formBold' align='right'>Total Claims</td>")
            Response.Write("<td class='formBold' align='right'>Claim Req Amount</td>")
            Response.Write("<td class='formBold' align='right'>Claim Aut Amount</td>")
            Response.Write("<td class='formBold' align='right'>Claim Pay Amount</td>")
            Response.Write("<td class='formBold' align='right'>LC Invoice Amount</td>")
            Response.Write("<td class='formBold' align='right'>Profit</td>")
            Response.Write("<td class='formBold' align='right'>Admin Fee</td>")
            Response.Write("<td class='formBold' align='right'>First Created</td>")
            Response.Write("</tr>")
            rowNumber = 1
            Do Until RecSet.EOF
                productID = RecSet.Fields("productID").Value
                product = RecSet.Fields("product").Value
                __date = RecSet.Fields("date").Value
                country = RecSet.Fields("country").Value
                countryCurrency = RecSet.Fields("countryCurrency").Value
                RS = Conn.Execute("Select Count(v.vehicleID) As vehicleCount From tblVehicle v Where v.productID = " & productID & sqlVehicleDateFrom & sqlVehicleDateTo & "")
                If Not RS.EOF Then 
                    vehicleCount = RS.Fields("vehicleCount").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Count(v.vehicleID) As vehicleActiveCount From tblVehicle v Where v.productID = " & productID & sqlVehicleDateFrom & sqlVehicleDateTo & " And v.active = 1 And v.completed = 0")
                If Not RS.EOF Then 
                    vehicleActiveCount = RS.Fields("vehicleActiveCount").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Count(c.claimID) As claimCount, Sum(c.requestedAmount) As requestedAmount, Sum(c.authorizedAmount) As authorizedAmount, Sum(c.paymentAmount) As paymentAmount From (tblClaim c INNER JOIN tblVehicle v On v.vehicleID = c.vehicleID) Where v.productID = " & productID & sqlClaimDateFrom & sqlClaimDateTo & "")
                If Not RS.EOF Then 
                    claimCount = RS.Fields("claimCount").Value
                    requestedAmount = RS.Fields("requestedAmount").Value
                    authorizedAmount = RS.Fields("authorizedAmount").Value
                    paymentAmount = RS.Fields("paymentAmount").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(pl.adminFee) As adminFee, Sum(pl.price) As price From (((tblVehicle v INNER JOIN tblInvoiceContent ic ON ic.vehicleID = v.vehicleID) INNER JOIN tblInvoice i ON i.invoiceID = ic.invoiceID) INNER JOIN tblPriceList pl ON pl.priceListID = ic.priceListID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & " And i.invoiceDate Is Not Null")
                If Not RS.EOF Then 
                    adminFee = RS.Fields("adminFee").Value
                    price = RS.Fields("price").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(pl.price) As creditPrice From (((tblVehicle v INNER JOIN tblInvoiceCreditContent ic ON ic.vehicleID = v.vehicleID) INNER JOIN tblInvoiceCredit i ON i.invoiceCreditID = ic.invoiceCreditID) INNER JOIN tblPriceList pl ON pl.priceListID = ic.priceListID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & " And i.invoiceDate Is Not Null")
                If Not RS.EOF Then 
                    creditPrice = RS.Fields("creditPrice").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(i.invoiceAmount) As distancePenalty From (tblInvoiceDistancePenalty i INNER JOIN tblVehicle v ON v.vehicleID = i.vehicleID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & "")
                If Not RS.EOF Then 
                    distancePenalty = RS.Fields("distancePenalty").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(i.invoiceAmount) As creditDistancePenalty From (tblInvoiceCreditDistancePenalty i INNER JOIN tblVehicle v ON v.vehicleID = i.vehicleID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & "")
                If Not RS.EOF Then 
                    creditDistancePenalty = RS.Fields("creditDistancePenalty").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(i.invoiceAmount) As durationPenalty From (tblInvoiceDurationPenalty i INNER JOIN tblVehicle v ON v.vehicleID = i.vehicleID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & "")
                If Not RS.EOF Then 
                    durationPenalty = RS.Fields("durationPenalty").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(i.invoiceAmount) As creditDurationPenalty From (tblInvoiceCreditDurationPenalty i INNER JOIN tblVehicle v ON v.vehicleID = i.vehicleID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & "")
                If Not RS.EOF Then 
                    creditDurationPenalty = RS.Fields("creditDurationPenalty").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(pl.price) As priceListModificationOld From (((tblVehicle v INNER JOIN tblInvoicePriceListModificationContent ic ON ic.vehicleID = v.vehicleID) INNER JOIN tblInvoicePriceListModification i ON i.invoicePriceListModificationID = ic.invoicePriceListModificationID) INNER JOIN tblPriceList pl ON pl.priceListID = ic.oldPriceListID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & " And i.invoiceDate Is Not Null")
                If Not RS.EOF Then 
                    priceListModificationOld = RS.Fields("priceListModificationOld").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(pl.price) As priceListModificationNew From (((tblVehicle v INNER JOIN tblInvoicePriceListModificationContent ic ON ic.vehicleID = v.vehicleID) INNER JOIN tblInvoicePriceListModification i ON i.invoicePriceListModificationID = ic.invoicePriceListModificationID) INNER JOIN tblPriceList pl ON pl.priceListID = ic.newPriceListID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & " And i.invoiceDate Is Not Null")
                If Not RS.EOF Then 
                    priceListModificationNew = RS.Fields("priceListModificationNew").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(i.invoiceAmount) As separate From (tblInvoiceSeparate i INNER JOIN tblVehicle v ON v.vehicleID = i.vehicleID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & " And i.vehicleID Is Not Null")
                If Not RS.EOF Then 
                    separate = RS.Fields("separate").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(i.invoiceAmount) As creditSeparate From (tblInvoiceCreditSeparate i INNER JOIN tblVehicle v ON v.vehicleID = i.vehicleID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & " And i.vehicleID Is Not Null")
                If Not RS.EOF Then 
                    creditSeparate = RS.Fields("creditSeparate").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(i.invoiceAmount) As serviceAccountBalance From (tblInvoiceServiceAccountBalance i INNER JOIN tblVehicle v ON v.vehicleID = i.vehicleID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & "")
                If Not RS.EOF Then 
                    serviceAccountBalance = RS.Fields("serviceAccountBalance").Value
                End IF
                RS.Close()
                RS = Nothing
                RS = Conn.Execute("Select Sum(i.invoiceAmount) As creditServiceAccountBalance From (tblInvoiceCreditServiceAccountBalance i INNER JOIN tblVehicle v ON v.vehicleID = i.vehicleID) Where v.productID = " & productID & sqlInvoiceDateFrom & sqlInvoiceDateTo & "")
                If Not RS.EOF Then 
                    creditServiceAccountBalance = RS.Fields("creditServiceAccountBalance").Value
                End IF
                RS.Close()
                RS = Nothing
                vehicleInActiveCount = CDbl(vehicleCount) - CDbl(vehicleActiveCount)
                If IsDBNull(requestedAmount) = true Then 
                    requestedAmount = 0
                End IF
                If IsDBNull(authorizedAmount) = true Then 
                    authorizedAmount = 0
                End IF
                If IsDBNull(paymentAmount) = true Then 
                    paymentAmount = 0
                End IF
                If IsDBNull(adminFee) = true Then 
                    adminFee = 0
                End IF
                If IsDBNull(price) = true Then 
                    price = 0
                End IF
                If IsDBNull(creditPrice) = true Then 
                    creditPrice = 0
                End IF
                price = CDbl(price) - CDbl(creditPrice)
                If IsDBNull(distancePenalty) = true Then 
                    distancePenalty = 0
                End IF
                price = price + distancePenalty
                If IsDBNull(creditDistancePenalty) = true Then 
                    creditDistancePenalty = 0
                End IF
                price = CDbl(price) - CDbl(creditDistancePenalty)
                If IsDBNull(durationPenalty) = true Then 
                    durationPenalty = 0
                End IF
                price = price + durationPenalty
                If IsDBNull(creditDurationPenalty) = true Then 
                    creditDurationPenalty = 0
                End IF
                price = CDbl(price) - CDbl(creditDurationPenalty)
                If IsDBNull(priceListModificationOld) = true Then 
                    priceListModificationOld = 0
                End IF
                If IsDBNull(priceListModificationNew) = true Then 
                    priceListModificationNew = 0
                End IF
                priceListModification = CDbl(priceListModificationNew) - CDbl(priceListModificationOld)
                price = price + priceListModification
                If IsDBNull(separate) = true Then 
                    separate = 0
                End IF
                price = price + separate
                If IsDBNull(creditSeparate) = true Then 
                    creditSeparate = 0
                End IF
                price = CDbl(price) - CDbl(creditSeparate)
                If IsDBNull(serviceAccountBalance) = true Then 
                    serviceAccountBalance = 0
                End IF
                price = price + serviceAccountBalance
                If IsDBNull(creditServiceAccountBalance) = true Then 
                    creditServiceAccountBalance = 0
                End IF
                price = CDbl(price) - CDbl(creditServiceAccountBalance)
                profitPrice = price
                profitPaymentAmount = paymentAmount
                If CInt(profitPrice) = 0 Then 
                    profitPrice = 1
                End IF
                If CInt(profitPaymentAmount) = 0 Then 
                    profitPaymentAmount = 1
                End IF
                profit = CDbl(profitPrice) / CDbl(profitPaymentAmount)
                profit = profit * 100
                profit = profit - 100
                vehicleActiveCountSum = vehicleActiveCountSum + vehicleActiveCount
                vehicleInActiveCountSum = vehicleInActiveCountSum + vehicleInActiveCount
                vehicleCountSum = vehicleCountSum + vehicleCount
                claimCountSum = claimCountSum + claimCount
                requestedAmountSum = requestedAmountSum + requestedAmount
                authorizedAmountSum = authorizedAmountSum + authorizedAmount
                paymentAmountSum = paymentAmountSum + paymentAmount
                adminFeeSum = adminFeeSum + adminFee
                priceSum = priceSum + price
                If rowNumber = 0 Then 
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 1
                Else
                    strGrey = ""
                    rowNumber = 0
                End IF
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & country & "</td>")
                Response.Write("<td>" & product & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(vehicleActiveCount), 0) & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(vehicleInActiveCount, 0) & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(vehicleCount), 0) & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(claimCount), 0) & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(requestedAmount), 2) & " " & countryCurrency & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(authorizedAmount), 2) & " " & countryCurrency & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(paymentAmount), 2) & " " & countryCurrency & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(price), 2) & " " & countryCurrency & "</td>")
                Response.Write("<td align='right'>" & FormatNumber(profit, 2) & " %</td>")
                Response.Write("<td align='right'>" & FormatNumber(CDbl(adminFee), 2) & " " & countryCurrency & "</td>")
                Response.Write("<td align='right'>" & Left(__date, 10) & "</td>")
                Response.Write("</tr>")
                If flushCounter = 50 Then 
                    Response.Flush()
                    flushCounter = 0
                Else
                    flushCounter = flushCounter + 1
                End IF
                RecSet.MoveNext()
            Loop
            profitPriceSum = priceSum
            profitPaymentAmountSum = paymentAmountSum
            If CInt(profitPriceSum) = 0 Then 
                profitPriceSum = 1
            End IF
            If CInt(profitPaymentAmountSum) = 0 Then 
                profitPaymentAmountSum = 1
            End IF
            profitSum = CDbl(profitPriceSum) / CDbl(profitPaymentAmountSum)
            profitSum = profitSum * 100
            profitSum = profitSum - 100
            If rowNumber = 0 Then 
                strGrey = " bgcolor='#eeeeee'"
            Else
                strGrey = ""
            End IF
            Response.Write("<tr" & strGrey & ">")
            Response.Write("<td colspan='2' class='formBold'>Total sum:</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(vehicleActiveCountSum), 0) & "</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(vehicleInActiveCountSum, 0) & "</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(vehicleCountSum), 0) & "</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(claimCountSum), 0) & "</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(requestedAmountSum), 2) & " " & countryCurrency & "</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(authorizedAmountSum), 2) & " " & countryCurrency & "</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(paymentAmountSum), 2) & " " & countryCurrency & "</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(priceSum), 2) & " " & countryCurrency & "</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(profitSum, 2) & " %</td>")
            Response.Write("<td class='formBold' align='right'>" & FormatNumber(CDbl(adminFeeSum), 2) & " " & countryCurrency & "</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("</tr>")
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='13'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Under()
    End IF
%>
